<?php
/**
 * Created by PhpStorm.
 * User: Sergey
 * Date: 14.03.14
 * Time: 18:18
 */

include_once BASE_DQ_PLUGIN_PATH . "classes/Config.php";
include_once BASE_DQ_PLUGIN_PATH . "classes/ConnectionFactory.php";
include_once BASE_DQ_PLUGIN_PATH . "classes/Quiz.php";

class QuizDao extends ConnectionFactory
{
    /**
     * @param $row
     * @return null|Quiz
     */
    public function fetch($row)
    {
        try {
            $quiz = new Quiz();
            $quiz->id = $row->id;
            $quiz->name = $row->name;
            $quiz->description = $row->description;
            $quiz->startDate = new \DateTime($row->start_date);
            $quiz->endDate = new \DateTime($row->end_date);
            $quiz->tutorId = $row->tutor_id;
            return $quiz;
        } catch (Exception $ex) {
            return null;
        }
    }

    /**
     * @return Quiz array
     */
    public function getAll()
    {
        $result = $this->getAllRows(Config::get()->getQuizzesTableName());
        $quizzes = array();
        foreach ($result as $row) {
            $quiz = $this->fetch($row);
            $quiz->groups = $this->getAllGroups($quiz->id);
            $quizzes[] = $quiz;
        }
        return $quizzes;
    }

    /**
     * @param int $id
     * @return null|Quiz
     */
    public function getById($id)
    {
        $row = $this->getRow(Config::get()->getQuizzesTableName(), $id);
        $quiz = $this->fetch($row);
        $quiz->groups = $this->getAllGroups($quiz->id);
        return $quiz;
    }

    /**
     * @param int $groupId
     * @return Quiz array
     */
    public function getByGroupId($groupId)
    {
        $quizzesTn = Config::get()->getQuizzesTableName();
        $quizzesToGroupTn = Config::get()->getRelQuizToGroupsTableName();
        $groupTn = Config::get()->getGroupsTableName();

        $query = "
        SELECT DISTINCT
            q.id, q.name, q.description, q.start_date, q.end_date, q.tutor_id
        FROM $quizzesTn AS q
            INNER JOIN $quizzesToGroupTn as qtg
                ON q.id = qtg.quiz_id
            INNER JOIN $groupTn g
                ON qtg.group_id = $groupId
        ORDER BY q.id";

        global $wpdb;
        $result = $wpdb->get_results($query);

        $quizzes = array();
        foreach ($result as $row) {
            $quizzes[] = $this->fetch($row);
        }
        return $quizzes;
    }

    /**
     * @param int $tutorId
     * @return array
     */
    public function getByTutorId($tutorId)
    {
        $table = Config::get()->getQuizzesTableName();
        $query = "SELECT * FROM $table WHERE tutor_id = $tutorId";

        global $wpdb;
        $result = $wpdb->get_results($query);

        $quizzes = array();
        foreach ($result as $row) {
            $quizzes[] = $this->fetch($row);
        }
        return $quizzes;
    }

    /**
     * @param Quiz $quiz
     * @return int
     */
    public function add(Quiz $quiz)
    {
        global $wpdb;
        $wpdb->insert(
            Config::get()->getQuizzesTableName(),
            array(
                "name" => $quiz->name,
                "description" => $quiz->description,
                "start_date" => $quiz->startDate->format("Y-m-d"),
                "end_date" => $quiz->endDate->format("Y-m-d"),
                "tutor_id" => $quiz->tutorId
            ),
            array("%s", "%s", "%s", "%s", "%d")
        );
        $quizId = $wpdb->insert_id;

        foreach ($quiz->groups as $groupId) {
            $this->addGroup($quiz->id, $groupId);
        }

        return $quizId;
    }

    /**
     * @param Quiz $quiz
     */
    public function edit(Quiz $quiz)
    {
        global $wpdb;
        $wpdb->update(
            Config::get()->getQuizzesTableName(),
            array(
                "name" => $quiz->name,
                "description" => $quiz->description,
                "start_date" => $quiz->startDate->format("Y-m-d"),
                "end_date" => $quiz->endDate->format("Y-m-d"),
                "tutor_id" => $quiz->tutorId
            ),
            array("id" => $quiz->id),
            array("%s", "%s", "%s", "%s", "%d"),
            array("%d")
        );

        $this->removeGroups($quiz->id);
        foreach ($quiz->groups as $groupId) {
            $this->addGroup($quiz->id, $groupId);
        }
    }

    /**
     * @param int $id
     */
    public function remove($id)
    {
        $this->removeRow(
            Config::get()->getQuizzesTableName(),
            $id
        );
    }

    public function getAllGroups($quizId)
    {
        global $wpdb;
        $table = Config::get()->getRelQuizToGroupsTableName();
        $result = $wpdb->get_results(
            "SELECT * FROM $table WHERE quiz_id=$quizId"
        );

        $groups = array();
        foreach ($result as $row) {
            $groups[] = $row->group_id;
        }
        return $groups;
    }

    /**
     * @param int $quizId
     * @param int $groupId
     */
    public function addGroup($quizId, $groupId)
    {
        global $wpdb;
        $wpdb->insert(
            Config::get()->getRelQuizToGroupsTableName(),
            array(
                "quiz_id" => $quizId,
                "group_id" => $groupId
            ),
            array("%d", "%d")
        );
    }

    /**
     * @param int $quizId
     * @param int $groupId
     */
    public function removeGroup($quizId, $groupId)
    {
        global $wpdb;
        $wpdb->delete(
            Config::get()->getRelQuizToGroupsTableName(),
            array(
                "quiz_id" => $quizId,
                "group_id" => $groupId
            ),
            array("%d", "%d")
        );
    }

    public function removeGroups($quizId)
    {
        global $wpdb;
        $wpdb->delete(
            Config::get()->getRelQuizToGroupsTableName(),
            array("quiz_id" => $quizId),
            array("%d")
        );
    }
}